﻿/***************************************************COPYRIGHT***************************************
* Copyright (c) 2008, Whiteboard-IT
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without modification, are permitted provided that 
* the following conditions are met:
*
* Redistributions of source code must retain the above copyright notice, this list of conditions and the following 
 * disclaimer.
*
* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following
 * disclaimer in the documentation and/or other materials provided with the distribution.
*
* Neither the name of Whiteboard-IT, LLC nor the names of its contributors may be used to endorse or promote 
 * products derived from this software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, 
 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 
 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON 
 * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
*****************************************************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Mite.MsSql
{
	public class MsSqlUtility
	{
		/// <summary>
		/// Returns the names of the tables in the database named <paramref name="databaseName"/>.
		/// </summary>
		public static IEnumerable<string> GetTableNamesInDatabase(string databaseName, IDbConnection conn)
		{
			const string tableNameSql =
				@"SELECT tables.table_name
	FROM information_schema.tables as tables";

			Guard.StringArgumentNotNullOrEmpty("databaseName", databaseName);
			Guard.ArgumentNotNull("conn", conn);

			IList<string> tableNames = new List<string>();

			using (IDbCommand command = conn.CreateCommand())
			{
				command.CommandText = tableNameSql;

				using (IDataReader dr = command.ExecuteReader())
				{
					while (dr.Read())
					{
						string name = dr["table_name"].ToString();

						tableNames.Add(name);
					}
				}
			}

			return tableNames;
		}

		/// <summary>
		/// Checks the existance of a column.
		/// </summary>
		/// <param name="connection">An open database connection.</param>
		/// <param name="database">Name of the database to check for the column.</param>
		/// <param name="table">Name of the table to check for the column.</param>
		/// <param name="column">Name of the column.</param>
		public static bool ColumnExists(IDbConnection connection, string database, string table, string column)
		{
			const string columnExistsSql =
				@"SELECT distinct 1 FROM information_schema.tables as tables 
	JOIN information_schema.columns as columns
    on columns.table_name = tables.table_name
    WHERE tables.table_name = @table_name and columns.COLUMN_NAME = @column;";

			Guard.StringArgumentNotNullOrEmpty("database", database);
			Guard.StringArgumentNotNullOrEmpty("table", table);
			Guard.StringArgumentNotNullOrEmpty("column", column);

			//Check for the existance of a column


			using (IDbCommand cmd = connection.CreateCommand())
			{
				cmd.CommandText = String.Format("USE {0}", database);
				cmd.ExecuteNonQuery();
			}
			using (IDbCommand cmd = connection.CreateCommand())
			{
				cmd.CommandText = columnExistsSql;
				cmd.Parameters.Add(new SqlParameter("@table_name", table));
				cmd.Parameters.Add(new SqlParameter("@column", column));
				IDataReader dr = cmd.ExecuteReader();
				return dr.Read();
			}
		}

		/// <summary>
		/// Checks if a column is of a specified data type.
		/// </summary>
		/// <param name="connection">An open database connection.</param>
		/// <param name="database">Name of the database that contains the column.</param>
		/// <param name="table">Name of the table that contains the column.</param>
		/// <param name="column">Name of the column.</param>
		/// <param name="datatype">Datatype expected.</param>
		public static bool ColumnIsOfDataType(IDbConnection connection, string database, string table, string column,
		                                      string datatype)
		{
			const string commandSql =
				@"SELECT distinct 1 FROM information_schema.tables as tables 
	JOIN information_schema.columns as columns
    on columns.table_name = tables.table_name
    WHERE tables.table_name = @table_name and columns.COLUMN_NAME = @column and DATA_TYPE = @datatype;";

			if (connection.State !=
			    ConnectionState.Open)
				throw new ArgumentException("IDbConnection expected to be open.", "connection");

			using (IDbCommand cmd = connection.CreateCommand())
			{
				cmd.CommandText = String.Format("USE {0}", database);
				cmd.ExecuteNonQuery();
			}
			using (IDbCommand cmd = connection.CreateCommand())
			{
				cmd.CommandText =
					commandSql;
				cmd.Parameters.Add(new SqlParameter("@table_name", table));
				cmd.Parameters.Add(new SqlParameter("@column", column));
				cmd.Parameters.Add(new SqlParameter("@datatype", datatype));
				IDataReader dr = cmd.ExecuteReader();
				return dr.Read();
			}
		}

		/// <summary>
		/// Checks the existance of a foreign key.
		/// </summary>
		/// <param name="connection">An open database connection.</param>
		/// <param name="database">Name of the database to search for the foreign key.</param>
		/// <param name="foreignKeyName">Name of the foreign key.</param>
		/// <returns><c>True</c> if the foreign key exists in the database; <c>false</c> if it does not.</returns>
		public static bool ForeignKeyExists(IDbConnection connection, string database, string foreignKeyName)
		{
			const string foreignKeyExistsSql =
				@"SELECT COUNT(CONSTRAINT_NAME) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE [CONSTRAINT_TYPE] = 'FOREIGN KEY' AND [CONSTRAINT_NAME] = '{0}';";

			if (connection.State !=
			    ConnectionState.Open)
				throw new ArgumentException("Expected open IDbConnection", "connection");

			using (IDbCommand cmd = connection.CreateCommand())
			{
				cmd.CommandText = String.Format(foreignKeyExistsSql, foreignKeyName);
				var count = (int) cmd.ExecuteScalar();

				return count > 0;
			}
		}

		/// <summary>
		/// Drops all tables in <paramref name="database"/>.
		/// </summary>
		/// <param name="connection">An open database connection.</param>
		/// <param name="database">Name of the database to remove the tables from.</param>
		public static void CleanDatabase(IDbConnection connection, string database)
		{
			const string getForeignKeysForTableSql =
				@"SELECT [CONSTRAINT_NAME] FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE [CONSTRAINT_TYPE] = 'FOREIGN KEY' AND [TABLE_NAME] = '{0}'";


			IEnumerable<string> tableNames = GetTableNamesInDatabase(database, connection);

			foreach (string tableName in tableNames)
			{
				var foreignKeys = new List<string>();

				// Drop constraints
				using (IDbCommand cmd = connection.CreateCommand())
				{
					cmd.CommandText = String.Format(getForeignKeysForTableSql, tableName);

					using (IDataReader dr = cmd.ExecuteReader())
					{
						while (dr.Read())
							foreignKeys.Add(dr[0].ToString());
					}
				}

				foreach (string fk in foreignKeys)
				{
					using (IDbCommand dropConstraintCommand = connection.CreateCommand())
					{
						dropConstraintCommand.CommandText = String.Format(@"ALTER TABLE {0} DROP CONSTRAINT {1}", tableName, fk);
						dropConstraintCommand.ExecuteNonQuery();
					}
				}
			}

			foreach (string tableName in tableNames)
			{
				// Drop table
				using (IDbCommand cmd = connection.CreateCommand())
				{
					cmd.CommandText = String.Format("DROP TABLE {0}", tableName);
					cmd.ExecuteNonQuery();
				}
			}
		}
	}
}